set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

with tmp as (
    select
         main.table_type          as table_type    --报表类型   
        ,dim.city_id              as city_id       --城市编码      
        ,dim.city_desc            as city_desc     --城市名称    
        ,dim.zone_id              as district_id   --区域(片区)编码
        ,dim.zone_name            as district_desc --区域(片区)名称
        ,dim.virt_code            as virt_code     --虚拟代理区编码
        ,dim.virt_name            as virt_name     --虚拟代理区名称
        ,main.zhund_cnt           as day_zhund_cnt --当日准点量
        ,main.all_cnt             as day_all_cnt   --当日应交件/签收量
        ,main.any_cnt             as day_any_cnt   --当日交件量
        ,main.dt as dt 
    from (
        select
             '交件准点率'          as table_type       --报表类型
            ,receive_network_code  as network_code     --网点编码
            ,receive_network_name  as network_name     --网点名称
            ,hand_over_in_time_num as zhund_cnt        --准点交件量
            ,should_hand_over_num  as all_cnt          --应交件量
            ,0                     as any_cnt          --当日交件量
            ,date_add(dt,1)        as dt               --分区日期
        from jms_dm.dm_waybill_collect_intime_taking_agg  --交件准点率揽收维度
        where dt between case when dayofmonth('{{ execution_date | cst_ds }}') = 1 or dayofmonth('{{ execution_date | cst_ds }}') = 2 then add_months(trunc('{{ execution_date | cst_ds }}','MM'),-1) else trunc('{{ execution_date | cst_ds }}','MM') end and '{{ execution_date | cst_ds }}'
        union all
        select
            '时效签收准点率'         as table_type        --报表类型
            ,final_sign_network_code as network_code      --网点编码
            ,final_sign_network_name as network_name      --网点名称
            ,sign_sum                as zhund_cnt         --应派件量
            ,need_sign_sum           as all_cnt           --时效签收量
            ,day_aging_sign_sum      as any_cnt           --当日时效签收量
            ,dt                      as dt                --分区日期
        from jms_dm.dm_network_jjj_aging_sign_detail_dt   --时效签收准点率
        where dt between trunc('{{ execution_date | cst_ds }}','MM') and '{{ execution_date | cst_ds }}'
    ) main
    left join (
        select *
        from jms_dim.dim_network_whole_massage
    ) dim on main.network_code = dim.code 
) 



insert overwrite table jms_dm.dm_effect_zhund_rate_dt
select
     table_type         --报表类型      
    ,district_id        --区域(片区)编码       
    ,district_desc      --区域(片区)名称       
    ,virt_code          --虚拟代理区编码       
    ,virt_name          --虚拟代理区名称      
    ,day_zhund_cnt      --当日准点量           
    ,day_all_cnt        --当日应交件/签收量    
    ,day_zhund_rate     --当日准点率
    ,month_zhund_cnt    --当月准点量
    ,month_all_cnt      --当月应交件/签收量
    ,round(month_zhund_cnt / month_all_cnt,4) as month_zhund_rate--当月应交件/签收量
    ,'区域' as type1      --区域/城市  
    ,null   as city_id    --城市编码     
    ,null   as city_desc  --城市名称  
    ,day_any_cnt          --当日交件量  
    ,month_any_cnt        --当月交件量
    ,dt 
from (
    select
         table_type          --报表类型 
        ,district_id         --区域(片区)编码
        ,district_desc       --区域(片区)名称
        ,virt_code           --虚拟代理区编码
        ,virt_name           --虚拟代理区名称
        ,day_zhund_cnt       --当日准点量
        ,day_all_cnt         --当日应交件/签收量
        ,day_zhund_rate      --当日准点率 
        ,day_any_cnt         --当日交件量
        ,sum(day_zhund_cnt) over(partition by table_type,district_id order by dt) as month_zhund_cnt --当月准点量
        ,sum(day_all_cnt)   over(partition by table_type,district_id order by dt) as month_all_cnt   --当月应交件/签收量
        ,sum(day_any_cnt)   over(partition by table_type,district_id order by dt) as month_any_cnt   --当月交件量
        ,dt
    from (    
        select   
             table_type      --报表类型             
            ,district_id     --区域(片区)编码       
            ,district_desc   --区域(片区)名称       
            ,virt_code       --虚拟代理区编码       
            ,virt_name       --虚拟代理区名称       
            ,sum(day_zhund_cnt) as day_zhund_cnt --当日准点量 
            ,sum(day_all_cnt)   as day_all_cnt   --当日应交件/签收量  
            ,sum(day_any_cnt)   as day_any_cnt   --当日交件量
            ,round(sum(day_zhund_cnt) / sum(day_all_cnt),4) as day_zhund_rate  --当日准点率
            ,dt
        from tmp 
        group by table_type    --报表类型
                ,district_id   --区域(片区)编码
                ,district_desc --区域(片区)名称
                ,virt_code     --虚拟代理区编码
                ,virt_name     --虚拟代理区名称
                ,dt            --分区日期
    ) a 
) a 
union all
select
     table_type             --报表类型      
    ,district_id            --区域(片区)编码       
    ,district_desc          --区域(片区)名称       
    ,virt_code              --虚拟代理区编码       
    ,virt_name              --虚拟代理区名称      
    ,day_zhund_cnt          --当日准点量           
    ,day_all_cnt            --当日应交件/签收量    
    ,day_zhund_rate         --当日准点率
    ,month_zhund_cnt        --当月准点量
    ,month_all_cnt          --当月应交件/签收量
    ,round(month_zhund_cnt / month_all_cnt,4) as month_zhund_rate--当月应交件/签收量
    ,'城市'    as type1      --区域/城市  
    ,city_id   as city_id    --城市编码     
    ,city_desc as city_desc  --城市名称    
    ,day_any_cnt             --当日交件量   
    ,month_any_cnt           --当月交件量
    ,dt 
from (
    select
         table_type          --报表类型 
        ,city_id             --城市编码 
        ,city_desc           --城市名称 
        ,district_id         --区域(片区)编码
        ,district_desc       --区域(片区)名称   
        ,virt_code           --虚拟代理区编码
        ,virt_name           --虚拟代理区名称
        ,day_zhund_cnt       --当日准点量
        ,day_all_cnt         --当日应交件/签收量
        ,day_zhund_rate      --当日准点率   
        ,day_any_cnt         --当日交件量   
        ,sum(day_zhund_cnt) over(partition by city_id,table_type,district_id order by dt  ) as month_zhund_cnt --当月准点量
        ,sum(day_all_cnt)   over(partition by city_id,table_type,district_id order by dt  ) as month_all_cnt   --当月应交件/签收量
        ,sum(day_any_cnt)   over(partition by city_id,table_type,district_id order by dt  ) as month_any_cnt   --当月交件量
        ,dt
    from (    
        select   
             table_type      --报表类型             
            ,city_id         --城市编码        
            ,city_desc       --城市名称     
            ,district_id     --区域(片区)编码
            ,district_desc   --区域(片区)名称   
            ,virt_code       --虚拟代理区编码       
            ,virt_name       --虚拟代理区名称       
            ,sum(day_zhund_cnt) as day_zhund_cnt --当日准点量 
            ,sum(day_all_cnt)   as day_all_cnt   --当日应交件/签收量  
            ,sum(day_any_cnt)   as day_any_cnt   --当日交件量
            ,round(sum(day_zhund_cnt) / sum(day_all_cnt),4) as day_zhund_rate  --当日准点率
            ,dt
        from tmp 
        group by table_type    --报表类型
                ,city_id       --城市编码 
                ,city_desc     --城市名称 
                ,district_id   --区域(片区)编码
                ,district_desc --区域(片区)名称
                ,virt_code     --虚拟代理区编码
                ,virt_name     --虚拟代理区名称
                ,dt            --分区日期
    ) a 
) a 
distribute by dt,pmod(hash(rand()),1) ;
